package action.report;

import hirondelle.date4j.DateTime;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import model.app.AppFinance;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.Region;

import service.report.FinancialStatusService;
import util.Time;
import util.Tool;

public class FinancialStatusAction {
	private String fileName;
	private String searchtime="";
	private String Year;
	private String Month;
	private FinancialStatusService financeService;
	private Map<Object,Object> financelist;
	public String execute() throws Exception{
		if(searchtime.equals("")){
			searchtime=Time.Timestamp2DateString(Time.CurrentTimestamp());
		}
		DateTime dt=new DateTime(searchtime);
		Year=dt.getYear().toString();
		Month=dt.getMonth().toString();
		financelist=financeService.getFinanceReportMap(searchtime);
		return "success";
	}
	
	public String reportExport(){
		return "report";
	}
	public InputStream getInputStream() throws Exception {
		if(searchtime.equals("")){
			searchtime=Time.Timestamp2DateString(Time.CurrentTimestamp());
		}
		DateTime dt=new DateTime(searchtime);
		Year=dt.getYear().toString();
		Month=dt.getMonth().toString();
		Map<Object,Object> list=financeService.getFinanceReportMap(searchtime);
		HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中  
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 
        HSSFSheet sheet = createSheet(workbook, style);
        addMapRow(sheet, 1, list, style);

		//下载文件名
        fileName=Tool.toChinese("财务情况调度汇总表.xls");
        
        //输出下载
		ByteArrayOutputStream baos = new ByteArrayOutputStream();  
		try {
			workbook.write(baos);
		} catch (IOException e) {
			e.printStackTrace();
		}
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);
        return bais;
	}
	@SuppressWarnings("deprecation")
	public int addMapRow(HSSFSheet sheet, int rowNumber, Map<Object, Object> list, HSSFCellStyle style){
		HSSFRow row = sheet.createRow(6);
        row.createCell((short)0).setCellValue("青岛市黄岛区");
        row.createCell((short)1).setCellValue("青岛市黄岛区瑞丰小额贷款有限公司");
        row.createCell((short)2).setCellValue(list.get("operatingfunds").toString());
        AppFinance finance=(AppFinance) list.get("finance");
        row.createCell((short)3).setCellValue(finance.getOperatregfinance().toString());
        row.createCell((short)4).setCellValue(finance.getOperatorgfinance().toString());
        row.createCell((short)5).setCellValue(finance.getAsset().toString());
        row.createCell((short)6).setCellValue(finance.getDebt().toString());
        row.createCell((short)7).setCellValue(list.get("ownershipinterest").toString());
        row.createCell((short)8).setCellValue(list.get("tax").toString());
        row.createCell((short)9).setCellValue(finance.getTaxincome().toString());
        row.createCell((short)10).setCellValue(finance.getTaxbusiness().toString());
        row.createCell((short)11).setCellValue(finance.getTaxstamp().toString());
        row.createCell((short)12).setCellValue(finance.getTaxother().toString());
        row.createCell((short)13).setCellValue(list.get("taxtotal").toString());
        row.createCell((short)14).setCellValue(list.get("taxincometotal").toString());
        row.createCell((short)15).setCellValue(list.get("taxbusinesstotal").toString());
        row.createCell((short)16).setCellValue(list.get("taxstamptotal").toString());
        row.createCell((short)17).setCellValue(list.get("taxothertotal").toString());
        row.createCell((short)18).setCellValue(list.get("interestmonth").toString());
        row.createCell((short)19).setCellValue(list.get("interesttotal").toString());
       // row.createCell((short)20).setCellValue(list.get("interestmonth").toString());
        //row.createCell((short)21).setCellValue(list.get("interestmonth").toString());
        row.createCell((short)22).setCellValue(finance.getProfitcurrent().toString());
        row.createCell((short)23).setCellValue(list.get("profitcurrenttotal").toString());
        row.createCell((short)24).setCellValue(finance.getProfitreserve().toString());
        row.createCell((short)25).setCellValue(finance.getWorkmanager().toString());
        row.createCell((short)26).setCellValue(finance.getWorkemployee().toString());
		return rowNumber;
	}
	
	@SuppressWarnings("deprecation")
	public HSSFSheet createSheet(HSSFWorkbook workbook, HSSFCellStyle style){
		HSSFSheet sheet = workbook.createSheet(Year+"年度"+Month+"月财务情况调度汇总表");
		
        //合并单元格
		sheet.addMergedRegion(new Region(0, (short)0, 0, (short)26));
		sheet.addMergedRegion(new Region(1, (short)0, 1, (short)26));
        sheet.addMergedRegion(new Region(2, (short)0, 4, (short)0));
        sheet.addMergedRegion(new Region(2, (short)1, 4, (short)1));
        sheet.addMergedRegion(new Region(2, (short)2, 2, (short)4));
        sheet.addMergedRegion(new Region(2, (short)5, 2, (short)7));
        sheet.addMergedRegion(new Region(2, (short)8, 2, (short)17));
        sheet.addMergedRegion(new Region(2, (short)18, 2, (short)21));
        sheet.addMergedRegion(new Region(2, (short)22, 2, (short)24));
        sheet.addMergedRegion(new Region(2, (short)25, 2, (short)26));
        sheet.addMergedRegion(new Region(3, (short)2, 4, (short)2));
        sheet.addMergedRegion(new Region(3, (short)3, 4, (short)3));
        sheet.addMergedRegion(new Region(3, (short)4, 4, (short)4));
        sheet.addMergedRegion(new Region(3, (short)5, 4, (short)5));
        sheet.addMergedRegion(new Region(3, (short)6, 4, (short)6));
        sheet.addMergedRegion(new Region(3, (short)7, 4, (short)7));
        sheet.addMergedRegion(new Region(3, (short)8, 3, (short)12));
        sheet.addMergedRegion(new Region(3, (short)13, 3, (short)17));
        sheet.addMergedRegion(new Region(3, (short)18, 4, (short)18));
        sheet.addMergedRegion(new Region(3, (short)19, 4, (short)19));
        sheet.addMergedRegion(new Region(3, (short)20, 4, (short)20));
        sheet.addMergedRegion(new Region(3, (short)21, 4, (short)21));
        sheet.addMergedRegion(new Region(3, (short)22, 4, (short)22));
        sheet.addMergedRegion(new Region(3, (short)23, 4, (short)23));
        sheet.addMergedRegion(new Region(3, (short)24, 4, (short)24));
        sheet.addMergedRegion(new Region(3, (short)25, 4, (short)25));
        sheet.addMergedRegion(new Region(3, (short)26, 4, (short)26));
           
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell = null;
        header = sheet.createRow(0);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString(Year+"年度"+Month+"月财务情况调度汇总表"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(1);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("单位：万元"));
        HSSFCellStyle style1 = workbook.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 左右居中  
		style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        cell.setCellStyle(style1);
        
        header = sheet.createRow(2);
        cell = header.createCell((short)0);
        cell.setCellValue(new HSSFRichTextString("地区"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)1);
        cell.setCellValue(new HSSFRichTextString("名称"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)2);
        cell.setCellValue(new HSSFRichTextString("营运资本"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)5);
        cell.setCellValue(new HSSFRichTextString("资产负债情况"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)8);
        cell.setCellValue(new HSSFRichTextString("税收缴纳情况"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)18);
        cell.setCellValue(new HSSFRichTextString("营业收入"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)22);
        cell.setCellValue(new HSSFRichTextString("营业利润"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)25);
        cell.setCellValue(new HSSFRichTextString("吸收就业"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(3);               
        cell = header.createCell((short)2);
        cell.setCellValue(new HSSFRichTextString("小计"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)3);
        cell.setCellValue(new HSSFRichTextString("其中：注册资本"));
        cell.setCellStyle(style);
        cell = header.createCell((short)4);
        cell.setCellValue(new HSSFRichTextString("金融机构融入资金"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)5);
        cell.setCellValue(new HSSFRichTextString("资产"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)6);
        cell.setCellValue(new HSSFRichTextString("负债"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)7);
        cell.setCellValue(new HSSFRichTextString("所有者权益"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)8);
        cell.setCellValue(new HSSFRichTextString("当月上缴"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("本年累计上缴"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)18);
        cell.setCellValue(new HSSFRichTextString("当月贷款利息收入"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)19);
        cell.setCellValue(new HSSFRichTextString("本年累计贷款利息收入"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)20);
        cell.setCellValue(new HSSFRichTextString("当月手续费、佣金等其他收入"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)21);
        cell.setCellValue(new HSSFRichTextString("本年累计手续费、佣金等其他收入"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)22);
        cell.setCellValue(new HSSFRichTextString("当月营业利润"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)23);
        cell.setCellValue(new HSSFRichTextString("本年累计营业利润"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)24);
        cell.setCellValue(new HSSFRichTextString("提取一般备用金余额"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)25);
        cell.setCellValue(new HSSFRichTextString("公司高管"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)26);
        cell.setCellValue(new HSSFRichTextString("一般员工"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(4);
        cell = header.createCell((short)8);
        cell.setCellValue(new HSSFRichTextString("小计"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)9);
        cell.setCellValue(new HSSFRichTextString("企业所得税"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)10);
        cell.setCellValue(new HSSFRichTextString("营业税及附加"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)11);
        cell.setCellValue(new HSSFRichTextString("印花税"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)12);
        cell.setCellValue(new HSSFRichTextString("其他"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)13);
        cell.setCellValue(new HSSFRichTextString("小计"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)14);
        cell.setCellValue(new HSSFRichTextString("企业所得税"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)15);
        cell.setCellValue(new HSSFRichTextString("营业税及附加"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)16);
        cell.setCellValue(new HSSFRichTextString("印花税"));
        cell.setCellStyle(style);
        
        cell = header.createCell((short)17);
        cell.setCellValue(new HSSFRichTextString("其他"));
        cell.setCellStyle(style);
        
        header = sheet.createRow(5);
        for(int i=0;i<27;i++){
        	cell = header.createCell((short)i);
            cell.setCellValue(i+1);
            cell.setCellStyle(style);
        }
        return sheet;
	}

	public String getSearchtime() {
		return searchtime;
	}

	public void setSearchtime(String searchtime) {
		this.searchtime = searchtime;
	}

	public FinancialStatusService getFinanceService() {
		return financeService;
	}

	public void setFinanceService(FinancialStatusService financeService) {
		this.financeService = financeService;
	}

	public Map<Object,Object> getFinancelist() {
		return financelist;
	}

	public void setFinancelist(Map<Object,Object> financelist) {
		this.financelist = financelist;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public String getYear() {
		return Year;
	}

	public void setYear(String year) {
		Year = year;
	}

	public String getMonth() {
		return Month;
	}

	public void setMonth(String month) {
		Month = month;
	}

}
